package henu.impl;

import java.sql.PreparedStatement;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import henu.IF.ManagementQuestionIF;
import henu.bean.ChoiceQuestion;
import henu.bean.JudgeQuestion;
import henu.bean.MultiChoiceQuestion;
import henu.others.subject;
import henu.util.DbcpPool;

public class ManagementQuestionImpl implements ManagementQuestionIF{


	@Override
	public boolean addchoiceQuestion(ChoiceQuestion cq) {
		String sql = "INSERT INTO choicequestion (c_question,c_choiceA,"
				+ "c_choiceB,c_choiceC"
				+ ",c_choiceD,c_answer,c_subjectid) "
				+ "VALUES (?,?,?,?,?,?,?)";
		PreparedStatement ps = DbcpPool.executePreparedStatement(sql);
		int result = 0 ;
		try {
			ps.setString(1, cq.getC_question());
			ps.setString(2, cq.getC_choiceA());
			ps.setString(3, cq.getC_choiceB());
			ps.setString(4, cq.getC_choiceC());
			ps.setString(5, cq.getC_choiceD());
			ps.setString(6, cq.getC_answer());
			ps.setInt(7, cq.getC_subjectid());
			result = ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbcpPool.close();
		if(result>0)
		return true;
		else 
		return false;
	}

	@Override
	public boolean addjudgeQuestion(JudgeQuestion jq) {
		String sql = "INSERT INTO judgequestion (j_question,j_answer,"
				+ "j_subjectid) "
				+ "VALUES (?,?,?)";
		PreparedStatement ps = DbcpPool.executePreparedStatement(sql);
		int result = 0 ;
		try {
			ps.setString(1, jq.getJ_question());
			ps.setInt(2, jq.getJ_answer());
			ps.setInt(3, jq.getJ_subjectid());
			result = ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbcpPool.close();
		if(result>0)
		return true;
		else 
		return false;
	}

	@Override
	public List<ChoiceQuestion> findchoiceQuestion(String subjectid) {
		String sql = null;
		if(subjectid==null) //判断题型
			sql = "select * from choicequestion";
		else 
			sql = "select * from choicequestion where c_subjectid='"+subjectid+"'";
		List<ChoiceQuestion> list = new ArrayList<ChoiceQuestion>();
		ResultSet rs = null;
        String sb = "";
		try{
			rs = DbcpPool.executeQuery(sql);
			while(rs.next()){
			ChoiceQuestion cq = new ChoiceQuestion();
			cq.setC_question(rs.getString("c_question"));
			cq.setC_choiceA(rs.getString("c_choiceA"));
			cq.setC_choiceB(rs.getString("c_choiceB"));
			cq.setC_choiceC(rs.getString("c_choiceC"));
			cq.setC_choiceD(rs.getString("c_choiceD"));
			cq.setC_id(rs.getInt("c_id"));
			cq.setC_answer(rs.getString("c_answer"));
			cq.setC_subjectid(rs.getInt("c_subjectid"));
			subject sj = new subject();
			cq.setC_subjectname(sj.getSubjectName(rs.getInt("c_subjectid")));
			list.add(cq);
			}
			rs.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		DbcpPool.close();
		return list;
		
	}

	@Override
	public boolean deletechoiceQuestion(String c_id) {
		int result = 0;
		String sql = "delete from choicequestion where c_id='"+c_id+"'";
		result = DbcpPool.executeUpdate(sql);
		if(result>0)
			return true;
		else 
		    return false;
	}

	@Override
	public boolean modifychoiceQuestion(ChoiceQuestion cq) {
		String sql = null;
		sql = "update choicequestion set c_question=?,c_choiceA=?,c_choiceB=?,"
				+"c_choiceC=?,c_choiceD=?,c_answer=? where c_id=?";
	    PreparedStatement ps = DbcpPool.executePreparedStatement(sql);
	    int result = 0;
	    try {
		ps.setString(1, cq.getC_question());
		ps.setString(2, cq.getC_choiceA());
		ps.setString(3, cq.getC_choiceB());
		ps.setString(4, cq.getC_choiceC());
		ps.setString(5, cq.getC_choiceD());
		ps.setString(6, cq.getC_answer());
		ps.setInt(7, cq.getC_id());
		result = ps.executeUpdate();
		ps.close();
	    } catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	    }
	    DbcpPool.close();
	   if(result>0)
	   return true;
	   else 
	   return false;
	}

	@Override
	public List<JudgeQuestion> findjudgeQuestion(String subjectid) {
		String sql = null;
		if(subjectid==null) //判断题型
			sql = "select * from judgequestion";
		else 
			sql = "select * from judgequestion where j_subjectid='"+subjectid+"'";
		List<JudgeQuestion> list = new ArrayList<JudgeQuestion>();
		ResultSet rs = null;
        String sb = "";
		try{
			rs = DbcpPool.executeQuery(sql);
			while(rs.next()){
			JudgeQuestion jq = new JudgeQuestion();
			jq.setJ_question(rs.getString("j_question"));
			jq.setJ_id(rs.getInt("j_id"));
			jq.setJ_answer(rs.getInt("j_answer"));
			jq.setJ_subjectid(rs.getInt("j_subjectid"));
			subject sj = new subject();
			jq.setJ_subjectname(sj.getSubjectName(rs.getInt("j_subjectid")));
			list.add(jq);
			}
			rs.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		DbcpPool.close();
		return list;
	}

	@Override
	public boolean deletejudgeQuestion(String j_id) {
		int result = 0;
		String sql = "delete from judgequestion where j_id='"+j_id+"'";
		result = DbcpPool.executeUpdate(sql);
		if(result>0)
			return true;
		else 
		    return false;
	}

	@Override
	public boolean modifyjudgeQuestion(JudgeQuestion jq) {
		String sql = null;
		sql = "update judgequestion set j_question=?,"
				+"j_answer=? where j_id=?";
	    PreparedStatement ps = DbcpPool.executePreparedStatement(sql);
	    int result = 0;
	    try {
		ps.setString(1, jq.getJ_question());
		ps.setInt(2, jq.getJ_answer());
		ps.setInt(3, jq.getJ_id());
		result = ps.executeUpdate();
		ps.close();
	    } catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	    }
	    DbcpPool.close();
	   
	   if(result>0)
	       return true;
	   else 
	       return false;
	}

	// 根据传入页码和容量获取题目
	@Override
	public List<ChoiceQuestion> findchoiceQuestionByPage(String subjectid ,String orderName ,int pagenum, int curPage) {
		String sql = null;
        int startNum = (curPage - 1) * pagenum;
		sql = "SELECT * FROM choicequestion WHERE c_subjectid = " + subjectid + " ORDER BY " + orderName + " LIMIT " + String.valueOf(pagenum) + " OFFSET " + String.valueOf(startNum);
		List<ChoiceQuestion> list = new ArrayList<ChoiceQuestion>();
	    int result = 0;
		ResultSet rs = null;
        String sb = "";
		try{
			rs = DbcpPool.executeQuery(sql);
			while(rs.next()){
				ChoiceQuestion cq = new ChoiceQuestion();
				cq.setC_question(rs.getString("c_question"));
				cq.setC_choiceA(rs.getString("c_choiceA"));
				cq.setC_choiceB(rs.getString("c_choiceB"));
				cq.setC_choiceC(rs.getString("c_choiceC"));
				cq.setC_choiceD(rs.getString("c_choiceD"));
				cq.setC_id(rs.getInt("c_id"));
				cq.setC_answer(rs.getString("c_answer"));
				cq.setC_subjectid(rs.getInt("c_subjectid"));
				subject sj = new subject();
				cq.setC_subjectname(sj.getSubjectName(rs.getInt("c_subjectid")));
				list.add(cq);
			}
			rs.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		DbcpPool.close();
		return list;
	}
	

	// 增加多选题
	@Override
	public boolean addMultiChoiceQuestion(MultiChoiceQuestion mcq) {
		String sql = "INSERT INTO multichoicequestion (m_question,m_choiceA,"
				+ "m_choiceB,m_choiceC"
				+ ",m_choiceD,m_answer,m_subjectid) "
				+ "VALUES (?,?,?,?,?,?,?)";
		PreparedStatement ps = DbcpPool.executePreparedStatement(sql);
		int result = 0 ;
		try {
			ps.setString(1, mcq.getM_question());
			ps.setString(2, mcq.getM_choiceA());
			ps.setString(3, mcq.getM_choiceB());
			ps.setString(4, mcq.getM_choiceC());
			ps.setString(5, mcq.getM_choiceD());
			ps.setString(6, mcq.getM_answer());
			ps.setInt(7, mcq.getM_subjectid());
			result = ps.executeUpdate();
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DbcpPool.close();
		if(result>0)
		return true;
		else 
		return false;
	}
	
	@Override
	public List<MultiChoiceQuestion> findMultiChoiceQuestion(String subjectid) {
		String sql = null;
		if(subjectid==null)
			sql = "select * from multichoicequestion";
		else 
			sql = "select * from multichoicequestion where m_subjectid='"+subjectid+"'";
		List<MultiChoiceQuestion> list = new ArrayList<MultiChoiceQuestion>();
		ResultSet rs = null;
        String sb = "";
		try{
			rs = DbcpPool.executeQuery(sql);
			while(rs.next()){
			MultiChoiceQuestion cq = new MultiChoiceQuestion();
			cq.setM_question(rs.getString("m_question"));
			cq.setM_choiceA(rs.getString("m_choiceA"));
			cq.setM_choiceB(rs.getString("m_choiceB"));
			cq.setM_choiceC(rs.getString("m_choiceC"));
			cq.setM_choiceD(rs.getString("m_choiceD"));
			cq.setM_id(rs.getInt("m_id"));
			cq.setM_answer(rs.getString("m_answer"));
			cq.setM_subjectid(rs.getInt("m_subjectid"));
			subject sj = new subject();
			cq.setM_subjectname(sj.getSubjectName(rs.getInt("m_subjectid")));
			list.add(cq);
			}
			rs.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		DbcpPool.close();
		return list;
	}
	@Override
	public List<MultiChoiceQuestion> findMultiChoiceQuestionByPage(String subjectid, String orderName, int pagenum,
			int curPage) {
		String sql = null;
        int startNum = (curPage - 1) * pagenum;
		sql = "SELECT * FROM multichoicequestion WHERE m_subjectid = " + subjectid + " ORDER BY " + orderName + " LIMIT " + String.valueOf(pagenum) + " OFFSET " + String.valueOf(startNum);
		List<MultiChoiceQuestion> list = new ArrayList<MultiChoiceQuestion>();
	    int result = 0;
		ResultSet rs = null;
        String sb = "";
		try{
			rs = DbcpPool.executeQuery(sql);
			while(rs.next()){
				MultiChoiceQuestion cq = new MultiChoiceQuestion();
				cq.setM_question(rs.getString("m_question"));
				cq.setM_choiceA(rs.getString("m_choiceA"));
				cq.setM_choiceB(rs.getString("m_choiceB"));
				cq.setM_choiceC(rs.getString("m_choiceC"));
				cq.setM_choiceD(rs.getString("m_choiceD"));
				cq.setM_id(rs.getInt("m_id"));
				cq.setM_answer(rs.getString("m_answer"));
				cq.setM_subjectid(rs.getInt("m_subjectid"));
				subject sj = new subject();
				cq.setM_subjectname(sj.getSubjectName(rs.getInt("m_subjectid")));
				list.add(cq);
			}
			rs.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		DbcpPool.close();
		return list;
	}
	@Override
	public boolean deleteMultiChoiceQuestion(String m_id) {
		int result = 0;
		String sql = "delete from multichoicequestion where m_id='"+m_id+"'";
		result = DbcpPool.executeUpdate(sql);
		if(result>0)
			return true;
		else 
		    return false;
	}
	@Override
	public boolean modifyMultiChoiceQuestion(MultiChoiceQuestion cq) {
		String sql = null;
		sql = "update multichoicequestion set m_question=?,m_choiceA=?,m_choiceB=?,"
				+"m_choiceC=?,m_choiceD=?,m_answer=? where m_id=?";
	    PreparedStatement ps = DbcpPool.executePreparedStatement(sql);
	    int result = 0;
	    try {
			ps.setString(1, cq.getM_question());
			ps.setString(2, cq.getM_choiceA());
			ps.setString(3, cq.getM_choiceB());
			ps.setString(4, cq.getM_choiceC());
			ps.setString(5, cq.getM_choiceD());
			ps.setString(6, cq.getM_answer());
			ps.setInt(7, cq.getM_id());
			result = ps.executeUpdate();
			ps.close();
	    } catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	    }
	    DbcpPool.close();
	   if(result>0)
	   return true;
	   else 
	   return false;
	}

	@Override
	public List<JudgeQuestion> findjudgeQuestionByPage(String subjectid, String orderName, int pagenum, int curPage) {
		String sql = null;
        int startNum = (curPage - 1) * pagenum;
		sql = "SELECT * FROM judgequestion WHERE j_subjectid = " + subjectid + " ORDER BY " + orderName + " LIMIT " + String.valueOf(pagenum) + " OFFSET " + String.valueOf(startNum);
		List<JudgeQuestion> list = new ArrayList<JudgeQuestion>();
	    int result = 0;
		ResultSet rs = null;
        String sb = "";
		try{
			rs = DbcpPool.executeQuery(sql);
			while(rs.next()){
				JudgeQuestion cq = new JudgeQuestion();
				cq.setJ_question(rs.getString("j_question"));
				cq.setJ_id(rs.getInt("j_id"));
				cq.setJ_answer(rs.getInt("j_answer"));
				cq.setJ_subjectid(rs.getInt("j_subjectid"));
				subject sj = new subject();
				cq.setJ_subjectname(sj.getSubjectName(rs.getInt("j_subjectid")));
				list.add(cq);
			}
			rs.close();
		}catch(Exception e)
		{
			e.printStackTrace();
		}
		DbcpPool.close();
		return list;
	}

}
